package com.ncmem.up6.sql;

import com.ncmem.up6.utils.ConfigReader;
import com.ncmem.up6.utils.DataBaseType;
import org.apache.commons.lang.StringUtils;

public class SqlBuilder {
    public static SqlBuilder build() { return new SqlBuilder(); }
    /// <summary>
    /// 拼接SQL语句
    /// </summary>
    /// <param name="table"></param>
    /// <param name="fields"></param>
    /// <param name="where"></param>
    /// <returns></returns>
    public String select(String table, String fields, String where)
    {
        String sql_where = "";
        if (!StringUtils.isEmpty(where.trim())) sql_where = String.format("where %x", where);
        String sql = String.format("select %s from %s %s", fields, table, sql_where);
        return sql;
    }

    public String select(String table, String fields, SqlParam[] where)
    {
        String sql = String.format("select %s from %s where %s",
                fields,
                table,
                this.wheres(where));
        return sql;
    }

    public String select(String table, String fields, SqlWhere where)
    {
        String sql = String.format("select %s from %s %s",
                fields,
                table,
                where.toSqlWhere("and"));
        return sql;
    }

    public String select(String table, SqlParam[] fields, SqlWhere where)
    {
        String sql = String.format("select %s from %s %s",
                this.defines(fields),
                table,
                where.toSqlWhere("and"));
        return sql;
    }

    public String select(String table, SqlParam[] fields, SqlWhere where,SqlSort sort)
    {
        String sql = String.format("select %s from %s %s %s",
                this.defines(fields),
                table,
                where.toSqlWhere("and"),
                sort.toSql());
        return sql;
    }

    public String select(String table, SqlParam[] fields, SqlWhere where,int top)
    {
        if(ConfigReader.dbType()== DataBaseType.Oracle) return this.selectTopOracle(table,fields,where,top);
        else if(ConfigReader.dbType()== DataBaseType.MySQL) return this.selectTopMySQL(table,fields,where,top);

        String tops = String.format(" top %d ", top); ;
        if (0 == top) tops = "";

        String sql = String.format("select %s %s from %s %s",
                tops,
                this.defines(fields),
                table,
                where.toSqlWhere("and"));
        return sql;
    }

    public String selectTopMySQL(String table,SqlParam[] cols,SqlWhere w,int top)
    {
        String tops = String.format(" limit 0,%d ", top); ;
        if (0 == top) tops = "";

        String sql = String.format("select %s from %s %s %s",
                this.defines(cols),
                table,
                w.toSqlWhere("and"),
                tops
                );
        return sql;
    }

    public String selectTopOracle(String table,SqlParam[] cols,SqlWhere w,int top)
    {
        String tops = String.format(" and rownum <=%d ", top); ;
        if (0 == top) tops = "";

        String sql = String.format("select %s from %s %s %s",
                this.defines(cols),
                table,
                w.toSqlWhere("and"),
                tops
        );
        return sql;
    }

    public String select(String table, SqlParam[] fields, SqlParam[] where)
    {
        String sql = String.format("select %s from %s where %s",
                this.defines(fields),
                table,
                this.wheres(where));
        return sql;
    }

    public String insert(String table, SqlParam[] fields)
    {
        String sql = String.format("insert into %s ( %s ) values( %s )",
                table,
                this.defines(fields),
                this.prameters(fields)
        );
        return sql;
    }

    public String update(String table, SqlParam[] fields, SqlParam[] where)
    {
        String sql = String.format("update %s set %s where %s",
                table,
                this.setter(fields),
                this.wheres(where)
        );
        return sql;
    }

    public String update(String table, SqlSeter st, SqlWhere where)
    {
        String sql = String.format("update %s set %s %s",
                table,
                st.toSql(),
                where.toSqlWhere("and")
        );
        return sql;
    }

    public String update(String table, SqlSeter st, SqlParam[] where)
    {
        String sql = String.format("update %s set %s where %s",
                table,
                st.toSql(),
                this.wheres(where)
        );
        return sql;
    }

    public String update(String table, SqlParam[] fields, SqlWhere where)
    {
        String sql = String.format("update %s set %s %s",
                table,
                this.setter(fields),
                where.empty() ? "" : " where "+where.toSql("and")
        );
        return sql;
    }

    public String delete(String table, SqlParam[] fields)
    {
        String sql = String.format("delete from %s where %s",
                table,
                this.wheres(fields)
        );
        return sql;
    }

    public String delete(String table, SqlWhere w)
    {
        String sql = String.format("delete from %s %s",
                table,
                w.toSqlWhere("and")
        );
        return sql;
    }

    /// <summary>
    /// 声明化,a,b,c,d,e,f,g
    /// </summary>
    /// <param name="ps"></param>
    /// <returns></returns>
    public String defines(SqlParam[] ps)
    {
        return SqlParam.mergeNames(ps,"%s",",");
    }

    /// <summary>
    /// 将字段转换成变量模式,@a,@b,@c,@d
    /// </summary>
    /// <param name="ps"></param>
    /// <returns></returns>
    public String prameters(SqlParam[] ps)
    {
        return SqlParam.mergeNames(ps, "?",",");
    }

    /// <summary>
    /// 将字段转换成赋值语句，a=?,b=?,c=?,d=?
    /// </summary>
    /// <param name="ps"></param>
    /// <returns></returns>
    public String setter(SqlParam[] ps) {
        return SqlParam.mergeNames(ps, "%s=?",",");
    }

    /// <summary>
    /// 将变量转换成条件语句,a=? and b=? and c=?
    /// </summary>
    /// <param name="ps"></param>
    /// <returns></returns>
    public String wheres(SqlParam[] ps)
    {
        return SqlParam.mergeNames(ps, "%s=?", " and ");
    }
}
